﻿using System;
using System.Data;
using System.Data.SqlClient;


namespace AvvaMobileNHibernateHelperKit.Framework
{
    public class Data
    {
        protected static SqlConnection conn = new SqlConnection();

        protected SqlCommand cmd = new SqlCommand();

        #region Public Methods

        public DataTable ExecuteReader(SqlCommand command)
        {
            try
            {
                using (command)
                {
                    //Assing the connection to command object.
                    command.Connection = conn;

                    //command.CommandType = CommandType.StoredProcedure;

                    //Create a data table to filloutn and return.
                    DataTable dt = new DataTable();

                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    //Open connection.
                    conn.Open();

                    //Create a pointer for datareader and assing to command execute reader.
                    SqlDataReader SqlDataReader = command.ExecuteReader();

                    //Create a pointer for the datacolumn.
                    DataColumn dc;

                    //Get columns from database then apply them to datatable.
                    for (int i = 0; i < SqlDataReader.FieldCount; i++)
                    {
                        dc = new DataColumn();
                        dc.ColumnName = SqlDataReader.GetName(i);
                        dc.DataType = SqlDataReader.GetFieldType(i);
                        dt.Columns.Add(dc);
                    }

                    //Create a pointer for the datarow.
                    DataRow dr;

                    //Read data.
                    while (SqlDataReader.Read())
                    {
                        dr = dt.NewRow();
                        for (int j = 0; j < SqlDataReader.FieldCount; j++)
                        {
                            dr[j] = SqlDataReader[j];
                        }
                        dt.Rows.Add(dr);
                    }

                    //Close datareader.
                    SqlDataReader.Close();

                    //Return Datatable with data.
                    return dt;
                }
            }
            finally
            {
                //Close connection.
                conn.Close();
            }
        }

        public void ExecuteNonQuery(SqlCommand command)
        {
            try
            {
                //Assing the connection to command object.
                command.Connection = conn;

                //Using sql command.
                using (command)
                {
                    command.CommandType = CommandType.StoredProcedure;
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    //Open connection.
                    conn.Open();

                    //Run command.
                    command.ExecuteNonQuery();


                }
            }
            finally
            {
                //Close connection.
                conn.Close();
            }

        }

        #endregion

        #region Public Properties

        public string ConnectionString
        {
            get { return conn.ConnectionString; }
            set { conn.ConnectionString = value; }
        }

        #endregion

        public DataTable GetDatabaseNames(string commandString)
        {
            cmd.CommandText = commandString;
            return ExecuteReader(cmd);
        }

        public void TestConnection()
        {
            conn.Open();
            conn.Close();
        }

        public DataTable ExecuteReader(string commandString)
        {
            cmd.CommandText = commandString;
            return ExecuteReader(cmd);
        }

        public DataTable GetTableColumnsInfo(string commandString)
        {
            SqlDataAdapter adp = new SqlDataAdapter(commandString, conn);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            return dt;
        }
    }
}
